package rai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.portlet.PortletSession;
import javax.portlet.RenderRequest;
import javax.portlet.RenderResponse;

import org.apache.log4j.Logger;

import rai.dto.EmployeeInfoDTO;
import rai.dto.ExperienceDTO;
import rai.dto.ProjectEmpInfoDTO;
import rai.dto.TakenCourseDTO;
import rai.util.Constants;

/**
 * Database operation for EmployeeInfo
 * 
 * @author Tien+Vu
 * 
 */
public class EmployeeInfoDAO {
    /** Employee Data property. */
    private EmployeeInfoDTO empInfo;
    /** Log property. */
    private static Logger log = Logger.getLogger("EmployeeInfoDAO.start");
    /**
     * Update Login Information.
     * @param empID
     */
    public void updateLogin (int empID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        PreparedStatement pStat = null;
        try {
            // query command
            DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
            java.util.Date date = new java.util.Date();
            query = "update user u set u.LastLogin ='" + dateFormat.format(date) 
                    + "' where u.empID = " + empID;
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            
            pStat.executeUpdate(query);
            
        } catch (final Exception sqlEx) {
            log.error("EmployeeInfoDAO.updateLogin" + sqlEx);
        } finally {
            try {
                
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EmployeeInfoDAO.updateLogin " + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.updateLogin.end");
        
    }
    
    /**
     * Load employee information and return to employee form.
     * 
     * @param ID
     * @param request
     * @param response
     * @return EmployeeInfoDTO
     */
    public EmployeeInfoDTO getEmpInfo(final int ID,
            final RenderRequest request, final RenderResponse response) {
        log.debug("EmployeeInfoDAO.getEmpInfo.start");
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            // query command
            query = "select * from employee where EmpID=?";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            pStat.setInt(1, ID);
            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {
                empInfo = new EmployeeInfoDTO();
                empInfo.setID(rs.getInt("EmpID"));
                empInfo.setName(rs.getString("Name"));
                empInfo.setBirthday(rs.getString("Birthday"));
                empInfo.setAddress(rs.getString("Address"));
                empInfo.setEmail(rs.getString("Email"));
                empInfo.setPhone(rs.getString("Phone"));
            }
            // return the result
            returnEmpInFo(empInfo, request, response);
        } catch (final Exception sqlEx) {
            log.error("EmployeeInfoDAO.getEmpInfo" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EmployeeInfoDAO.getEmpInfo " + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.getEmpInfo.end");
        return empInfo;
    }

    /**
     * Load Experience of Employee and return to Employee form.
     * 
     * @param ID
     * @param request
     * @param response
     * @return List ExperienceDTO
     */
    public List<ExperienceDTO> getExperience(final int ID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        final List<ExperienceDTO> List_exp = new ArrayList<ExperienceDTO>();
        log.debug("EmployeeInfoDAO.getExperience.start");
        try {
            // query command
            query = "select EmpID,d.DepartID, ComName,Address,Phone,DepartName,StartDate,EndDate,Role,de.Description  "
                    + "from departexp de,department d,company c,role r "
                    + "where EmpID=? and de.DepartID = d.DepartID and d.CompanyID=c.CompanyID and de.RoleID=r.RoleID";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            pStat.setInt(1, ID);
            rs = pStat.executeQuery();
            while (rs.next()) {
                final ExperienceDTO exp = new ExperienceDTO();
                exp.setDepartID(rs.getInt("DepartID"));
                exp.setCompName(rs.getString("ComName"));
                exp.setCompAddress(rs.getString("Address"));
                exp.setCompPhone(rs.getString("Phone"));
                exp.setDepartName(rs.getString("DepartName"));
                exp.setStartDate(rs.getString("StartDate"));
                exp.setEndDate(rs.getString("EndDate"));
                exp.setRole(rs.getString("Role"));
                exp.setDescription(rs.getString("Description"));
                List_exp.add(exp);
            }
            
        } catch (final Exception sqlEx) {
            log.error("AccessEmpData.getExp" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.getExp" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.getExperience.end");
        return List_exp;
    }

    /**
     * return employee information to employee info form.
     * @param EmpInfo
     * @param request
     * @param response
     */
    public void returnEmpInFo(final EmployeeInfoDTO EmpInfo,
            final RenderRequest request, final RenderResponse response) {
        request.getPortletSession().setAttribute(Constants.ID_PORT_SESS_ATT,
                EmpInfo.getID(), PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute(
                Constants.EMP_NAME_PORT_SESS_ATT, EmpInfo.getName(),
                PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute(
                Constants.BIRTHDAY_PORT_SESS_ATT, EmpInfo.getBirthday(),
                PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute(
                Constants.ADDRESS_PORT_SESS_ATT, EmpInfo.getAddress(),
                PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute(Constants.EMAIL_PORT_SESS_ATT,
                EmpInfo.getEmail(), PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute(Constants.PHONE_PORT_SESS_ATT,
                EmpInfo.getPhone(), PortletSession.APPLICATION_SCOPE);
        // set up Scope for Edit Mode
        request.getPortletSession().setAttribute("editEmpName",
                EmpInfo.getName(), PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute("editBirthday",
                EmpInfo.getBirthday(), PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute("editAddress",
                EmpInfo.getAddress(), PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute("editEmail",
                EmpInfo.getEmail(), PortletSession.APPLICATION_SCOPE);
        request.getPortletSession().setAttribute("editPhone",
                EmpInfo.getPhone(), PortletSession.APPLICATION_SCOPE);

    }

    /**
     * Load employee information and return to employee form.
     * 
     * @param ID
     * @param request
     * @param response
     * @return
     */
    public EmployeeInfoDTO getAnotherEmpInfo(final int ID,
            final RenderRequest request, final RenderResponse response) {
        log.debug("EmployeeInfoDAO.getEmpInfo.start");
        EmployeeInfoDTO empInfo = new EmployeeInfoDTO();
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            // query command
            query = "select * from employee where EmpID=?";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            pStat.setInt(1, ID);
            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {

                empInfo.setID(rs.getInt("EmpID"));
                empInfo.setName(rs.getString("Name"));
                empInfo.setBirthday(rs.getString("Birthday"));
                empInfo.setAddress(rs.getString("Address"));
                empInfo.setEmail(rs.getString("Email"));
                empInfo.setPhone(rs.getString("Phone"));
            }
            // return the result
            

        } catch (final Exception sqlEx) {
            log.error("EmployeeInfoDAO.getEmpInfo" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EmployeeInfoDAO.getEmpInfo " + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.getEmpInfo.end");
        return empInfo;
    }

    /**
     * Get project Employee Take.
     * 
     * @param empID
     * @return List ProjectEmpInfoDTO
     */
    public List<ProjectEmpInfoDTO> getProject(final int empID) {
        log.debug("EmployeeInfoDAO.getProject.start");
        final List<ProjectEmpInfoDTO> lstPrj = new ArrayList<ProjectEmpInfoDTO>();
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            // query command
            query = "select p.ProjectID, r.Role from project p, projectexp pe, role r where "
                    + "p.ProjectID=pe.ProjectID and pe.Role=r.RoleID and pe.EmpID="
                    + empID;
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);

            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {
                final ProjectEmpInfoDTO prj = new ProjectEmpInfoDTO();
                prj.setId(rs.getInt("ProjectID"));
                prj.setRole(rs.getString("Role"));
                lstPrj.add(prj);

            }
            
            for (int i = 0; i < lstPrj.size(); i++) {
                query = "select ProjectName, dp.DepartName \"DepartName\", PeriodFrom, PeriodTo,"
                        + " p.Description "
                        + "from project p,department dp, projectexp pe "
                        + "where pe.ProjectID=p.ProjectID and p.DepartID=dp.DepartID and "
                        + "p.ProjectID="
                        + lstPrj.get(i).getId();
                conn = rai.util.Connection.getConnection();
                // prepare statement
                pStat = conn.prepareStatement(query);
                ProjectEmpInfoDTO prj = lstPrj.get(i);    
                rs = pStat.executeQuery();
                while (rs.next()) {
                    
                    prj.setSname(rs.getString("ProjectName"));
                    prj.setDept(rs.getString("DepartName"));
                    prj.setStart(rs.getString("PeriodFrom"));
                    prj.setEnd(rs.getString("PeriodTo"));
                    prj.setDes(rs.getString("Description"));
                }
                                
                query = "select GROUP_CONCAT(l.Name) \"Language\" "
                    + "from pits_db.language l, project_language pl "
                    + "where pl.LangID = l.LangID and pl.ProjectID ="+prj.getId(); 
                
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setLang((rs.getString("Language")));
                }
                
                query = "select GROUP_CONCAT(l.Name) \"Database\" "
                    + "from pits_db.database l, project_database pl "
                    + "where pl.DatabaseID = l.DatabaseID and pl.ProjectID ="+prj.getId(); 
                
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setDatabase((rs.getString("Database")));
                }
               
                query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                    + "from pits_db.framework l, project_framework pl "
                    + "where pl.FrameID = l.FrameID and pl.ProjectID ="+prj.getId(); 
                
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setFrw((rs.getString("Framework")));
                }
                
                query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                    + "from pits_db.technology l, project_technology pl "
                    + "where pl.TechID = l.TechID and pl.ProjectID ="+prj.getId(); 
                conn = rai.util.Connection.getConnection();
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setFrw(prj.getFrw()+","+(rs.getString("Framework")));
                }
                
                    // return the result
          }
                

        } catch (final Exception sqlEx) {
            log.error("EmployeeInfoDAO.getEmpInfo" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EmployeeInfoDAO.getEmpInfo " + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.getProject.end");
        return lstPrj;
    }

    /**
     * Get course Employee Take.
     *
     * @param empID
     * @return List TakenCourseDTO
     */
    public List<TakenCourseDTO> getCourse(final int empID) {
        log.debug("EmployeeInfoDAO.getCourse.start");
        final List<TakenCourseDTO> lstCourse = new ArrayList<TakenCourseDTO>();
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        
        try {
            // query command
            query = "select c.CourseID, ce.Grade from course c, courseexp ce where "
                    + "c.CourseID=ce.CourseID and ce.EmpID=" + empID;
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);

            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {
                final TakenCourseDTO prj = new TakenCourseDTO();
                prj.setId(rs.getInt("CourseID"));
                prj.setGrade((rs.getString("Grade")));
                lstCourse.add(prj);

            }
            for (int i = 0; i < lstCourse.size(); i++) {
                query = "select p.Name \"CourseName\", dp.DepartName \"DepartName\", Period, "
                        + "DateIssued, p.Description "
                        + "from course p,department dp, courseexp pe "
                        + "where pe.CourseID=p.CourseID and p.DepartID=dp.DepartID and "
                        + "p.CourseID="
                        + lstCourse.get(i).getId();
                conn = rai.util.Connection.getConnection();
                // prepare statement
                pStat = conn.prepareStatement(query);

                rs = pStat.executeQuery();
                final TakenCourseDTO prj = lstCourse.get(i);
                while (rs.next()) {
                    
                    prj.setSname(rs.getString("CourseName"));
                    prj.setDept(rs.getString("DepartName"));
                    prj.setDate(rs.getString("DateIssued"));
                    prj.setPeriod(rs.getString("Period"));
                    prj.setDes(rs.getString("Description"));
                }
                query = "select GROUP_CONCAT(l.Name) \"Language\" "
                    + "from pits_db.language l, course_language pl "
                    + "where pl.LangID = l.LangID and pl.CourseID ="+prj.getId(); 
                conn = rai.util.Connection.getConnection();
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setLang((rs.getString("Language")));
                }
                
                query = "select GROUP_CONCAT(l.Name) \"Database\" "
                    + "from pits_db.database l, course_database pl "
                    + "where pl.DatabaseID = l.DatabaseID and pl.CourseID ="+prj.getId(); 
                
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setDatabase((rs.getString("Database")));
                }
               
                query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                    + "from pits_db.framework l, course_framework pl "
                    + "where pl.FrameID = l.FrameID and pl.CourseID ="+prj.getId(); 
                
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setFrw((rs.getString(1)));
                }
              
                query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                    + "from pits_db.technology l, course_technology pl "
                    + "where pl.TechID = l.TechID and pl.CourseID ="+prj.getId(); 
                
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery();
                while (rs.next()) {
                    prj.setFrw(prj.getFrw()+","+(rs.getString(1)));
                }
                
                    // return the result
          }
            

        } catch (final Exception sqlEx) {
            log.error("EmployeeInfoDAO.getEmpInfo" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EmployeeInfoDAO.getEmpInfo " + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.getProject.end");
        return lstCourse;
    }
    /**
     * Delete Emp Experience Info
     * @param lstDeptID
     * @param empID
     */
    public void delete_Exp(String[] lstDeptID, int empID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        PreparedStatement pStat = null;
        log.debug("EmployeeInfoDAO.deleteExp.start");
        try {
            for(int i=0;i<lstDeptID.length;i++) {
                // query command
                query = "delete from departexp "
                		+ "where EmpID=" + empID +" and DepartID=" 
                		+ lstDeptID[i];
                // get connection
                conn = rai.util.Connection.getConnection();
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
            }
        } catch (final Exception sqlEx) {
            log.error("Delete Exp" + sqlEx);
        } finally {
            try {
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.getExp" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.deleteExp.end");
   }
    /**
     * Delete Course
     * @param lstCourseID
     * @param empID
     */
    public void delete_Course(String[] lstCourseID, int empID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        PreparedStatement pStat = null;
        log.debug("EmployeeInfoDAO.deleteCourse.start");
        try {
            for(int i=0;i<lstCourseID.length;i++) {
                // query command
                query = "delete from courseexp "
                        + "where EmpID=" + empID +" and CourseID=" 
                        + lstCourseID[i];
                // get connection
                conn = rai.util.Connection.getConnection();
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
            }
        } catch (final Exception sqlEx) {
            log.error("Delete Course" + sqlEx);
        } finally {
            try {
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.getCourse" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.deleteCourse.end");
   }
   /**
    * Delete Project
    * @param lstPrjID
    * @param empID
    */
    public void delete_Prj(String[] lstPrjID, int empID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        PreparedStatement pStat = null;
        log.debug("EmployeeInfoDAO.deletePrj.start");
        try {
            for(int i=0;i<lstPrjID.length;i++) {
                // query command
                query = "delete from projectexp "
                        + "where EmpID=" + empID +" and ProjectID=" 
                        + lstPrjID[i];
                // get connection
                conn = rai.util.Connection.getConnection();
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
            }
        } catch (final Exception sqlEx) {
            log.error("Delete Prj" + sqlEx);
        } finally {
            try {
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.delPrj" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.deletePrj.end");
   }

}
